
--Create database

USE [master]
GO
IF NOT EXISTS (SELECT [name] FROM sys.databases WHERE name = N'DoanKhachSan')
BEGIN
CREATE DATABASE [DoanKhachSan] COLLATE SQL_Latin1_General_CP1_CI_AS
END
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'DoanKhachSan', @new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
BEGIN
	EXEC [DoanKhachSan].[dbo].[sp_fulltext_database] @action = 'enable'
END
GO
ALTER DATABASE [DoanKhachSan] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [DoanKhachSan] SET ANSI_NULLS OFF
GO
ALTER DATABASE [DoanKhachSan] SET ANSI_PADDING OFF
GO
ALTER DATABASE [DoanKhachSan] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [DoanKhachSan] SET ARITHABORT OFF
GO
ALTER DATABASE [DoanKhachSan] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [DoanKhachSan] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [DoanKhachSan] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [DoanKhachSan] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [DoanKhachSan] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [DoanKhachSan] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [DoanKhachSan] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [DoanKhachSan] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [DoanKhachSan] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [DoanKhachSan] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [DoanKhachSan] SET ENABLE_BROKER
GO
ALTER DATABASE [DoanKhachSan] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [DoanKhachSan] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [DoanKhachSan] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [DoanKhachSan] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [DoanKhachSan] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [DoanKhachSan] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [DoanKhachSan] SET READ_WRITE
GO
ALTER DATABASE [DoanKhachSan] SET RECOVERY SIMPLE
GO
ALTER DATABASE [DoanKhachSan] SET MULTI_USER
GO
ALTER DATABASE [DoanKhachSan] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [DoanKhachSan] SET DB_CHAINING OFF
GO

USE [DoanKhachSan]
GO

--Create Roles
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'public' AND type = 'R')
	CREATE ROLE [public] AUTHORIZATION [dbo]
GO

--Database Schemas

USE [DoanKhachSan]
GO
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'dbo')
EXEC sys.sp_executesql N'CREATE SCHEMA [dbo] AUTHORIZATION [dbo]'
GO
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'guest')
EXEC sys.sp_executesql N'CREATE SCHEMA [guest] AUTHORIZATION [guest]'
GO
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'INFORMATION_SCHEMA')
EXEC sys.sp_executesql N'CREATE SCHEMA [INFORMATION_SCHEMA] AUTHORIZATION [INFORMATION_SCHEMA]'
GO
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'sys')
EXEC sys.sp_executesql N'CREATE SCHEMA [sys] AUTHORIZATION [sys]'
GO
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'db_owner')
EXEC sys.sp_executesql N'CREATE SCHEMA [db_owner] AUTHORIZATION [db_owner]'
GO
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'db_accessadmin')
EXEC sys.sp_executesql N'CREATE SCHEMA [db_accessadmin] AUTHORIZATION [db_accessadmin]'
GO
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'db_securityadmin')
EXEC sys.sp_executesql N'CREATE SCHEMA [db_securityadmin] AUTHORIZATION [db_securityadmin]'
GO
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'db_ddladmin')
EXEC sys.sp_executesql N'CREATE SCHEMA [db_ddladmin] AUTHORIZATION [db_ddladmin]'
GO
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'db_backupoperator')
EXEC sys.sp_executesql N'CREATE SCHEMA [db_backupoperator] AUTHORIZATION [db_backupoperator]'
GO
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'db_datareader')
EXEC sys.sp_executesql N'CREATE SCHEMA [db_datareader] AUTHORIZATION [db_datareader]'
GO
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'db_datawriter')
EXEC sys.sp_executesql N'CREATE SCHEMA [db_datawriter] AUTHORIZATION [db_datawriter]'
GO
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'db_denydatareader')
EXEC sys.sp_executesql N'CREATE SCHEMA [db_denydatareader] AUTHORIZATION [db_denydatareader]'
GO
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'db_denydatawriter')
EXEC sys.sp_executesql N'CREATE SCHEMA [db_denydatawriter] AUTHORIZATION [db_denydatawriter]'
GO

--Table dbo.CHITIETHOADON

USE [DoanKhachSan]
GO

--Create table and its columns
CREATE TABLE [dbo].[CHITIETHOADON] (
	[MaChiTietHoaDon] [int] NOT NULL IDENTITY (1, 1),
	[MaChiTietPhieuThue] [int] NULL,
	[MaHoaDon] [int] NULL);
GO

--Table dbo.CHITIETPHIEUTHUE

USE [DoanKhachSan]
GO

--Create table and its columns
CREATE TABLE [dbo].[CHITIETPHIEUTHUE] (
	[MaChiTietPhieuThue] [int] NOT NULL IDENTITY (1, 1),
	[MaPhieuThue] [int] NULL,
	[MaKhachHang] [int] NULL);
GO

SET IDENTITY_INSERT [dbo].[CHITIETPHIEUTHUE] ON
GO
INSERT INTO [dbo].[CHITIETPHIEUTHUE] ([MaChiTietPhieuThue], [MaPhieuThue], [MaKhachHang])
	VALUES (1, 4, 2)

GO
INSERT INTO [dbo].[CHITIETPHIEUTHUE] ([MaChiTietPhieuThue], [MaPhieuThue], [MaKhachHang])
	VALUES (2, 5, 3)

GO
INSERT INTO [dbo].[CHITIETPHIEUTHUE] ([MaChiTietPhieuThue], [MaPhieuThue], [MaKhachHang])
	VALUES (3, 7, 5)

GO
SET IDENTITY_INSERT [dbo].[CHITIETPHIEUTHUE] OFF
GO

--Table dbo.HOADON

USE [DoanKhachSan]
GO

--Create table and its columns
CREATE TABLE [dbo].[HOADON] (
	[MaHoaDon] [int] NOT NULL IDENTITY (1, 1),
	[NgayTraPhong] [datetime] NULL,
	[TongTien] [float] NULL);
GO

--Table dbo.KHACHHANG

USE [DoanKhachSan]
GO

--Create table and its columns
CREATE TABLE [dbo].[KHACHHANG] (
	[MaKhachHang] [int] NOT NULL IDENTITY (1, 1),
	[TenKhachHang] [varchar](50) NULL,
	[CMND] [varchar](50) NULL,
	[DiaChi] [varchar](50) NULL,
	[MaLoaiKhach] [varchar](50) NULL);
GO

SET IDENTITY_INSERT [dbo].[KHACHHANG] ON
GO
INSERT INTO [dbo].[KHACHHANG] ([MaKhachHang], [TenKhachHang], [CMND], [DiaChi], [MaLoaiKhach])
	VALUES (1, N'thang', N'232434', N'd', N'T')

GO
INSERT INTO [dbo].[KHACHHANG] ([MaKhachHang], [TenKhachHang], [CMND], [DiaChi], [MaLoaiKhach])
	VALUES (2, N'duyen', N'23234', N'd', N't')

GO
INSERT INTO [dbo].[KHACHHANG] ([MaKhachHang], [TenKhachHang], [CMND], [DiaChi], [MaLoaiKhach])
	VALUES (3, N'Thang', N'23423', N'azd', N'KNN')

GO
INSERT INTO [dbo].[KHACHHANG] ([MaKhachHang], [TenKhachHang], [CMND], [DiaChi], [MaLoaiKhach])
	VALUES (4, N'Thang', N'23423', N'azd', N'KNN')

GO
INSERT INTO [dbo].[KHACHHANG] ([MaKhachHang], [TenKhachHang], [CMND], [DiaChi], [MaLoaiKhach])
	VALUES (5, N'Thang pro 3', N'23423', N'azd', N'KNN')

GO
INSERT INTO [dbo].[KHACHHANG] ([MaKhachHang], [TenKhachHang], [CMND], [DiaChi], [MaLoaiKhach])
	VALUES (6, N'Hai', N'23', N'we', N'KNN')

GO
INSERT INTO [dbo].[KHACHHANG] ([MaKhachHang], [TenKhachHang], [CMND], [DiaChi], [MaLoaiKhach])
	VALUES (7, N'a', N'3', N'a', N'KNN')

GO
SET IDENTITY_INSERT [dbo].[KHACHHANG] OFF
GO

--Table dbo.LOAIKHACH

USE [DoanKhachSan]
GO

--Create table and its columns
CREATE TABLE [dbo].[LOAIKHACH] (
	[MaLoaiKhach] [varchar](50) NOT NULL,
	[TenLoaiKhach] [varchar](50) NULL,
	[HeSoKhach] [float] NULL);
GO

INSERT INTO [dbo].[LOAIKHACH] ([MaLoaiKhach], [TenLoaiKhach], [HeSoKhach])
	VALUES (N'KTN', N'Khach Trong Nuoc', CAST ('1' AS float))

GO
INSERT INTO [dbo].[LOAIKHACH] ([MaLoaiKhach], [TenLoaiKhach], [HeSoKhach])
	VALUES (N'KNN', N'Nuoc Ngoai', CAST ('1.5' AS float))

GO

--Table dbo.LOAIPHONG

USE [DoanKhachSan]
GO

--Create table and its columns
CREATE TABLE [dbo].[LOAIPHONG] (
	[MaLoaiPhong] [varchar](50) NOT NULL,
	[TenLoaiPhong] [varchar](50) NULL,
	[GiaLoaiPhong] [int] NULL,
	[SoKhachToiDa] [int] NULL,
	[TyLePhuThu] [float] NULL);
GO

INSERT INTO [dbo].[LOAIPHONG] ([MaLoaiPhong], [TenLoaiPhong], [GiaLoaiPhong], [SoKhachToiDa], [TyLePhuThu])
	VALUES (N'PLA', N'Phong Loai A', 150000, 3, CAST ('1.5' AS float))

GO
INSERT INTO [dbo].[LOAIPHONG] ([MaLoaiPhong], [TenLoaiPhong], [GiaLoaiPhong], [SoKhachToiDa], [TyLePhuThu])
	VALUES (N'PLB', N'Phong Loai B', 170000, 3, CAST ('1.5' AS float))

GO

--Table dbo.PHIEUTHUEPHONG

USE [DoanKhachSan]
GO

--Create table and its columns
CREATE TABLE [dbo].[PHIEUTHUEPHONG] (
	[MaPhieuThue] [int] NOT NULL IDENTITY (1, 1),
	[MaPhong] [varchar](50) NULL,
	[NgayBatDauThue] [datetime] NULL);
GO

SET IDENTITY_INSERT [dbo].[PHIEUTHUEPHONG] ON
GO
INSERT INTO [dbo].[PHIEUTHUEPHONG] ([MaPhieuThue], [MaPhong], [NgayBatDauThue])
	VALUES (1, N'P01A', CAST(0x0000a19300000000 AS datetime))

GO
INSERT INTO [dbo].[PHIEUTHUEPHONG] ([MaPhieuThue], [MaPhong], [NgayBatDauThue])
	VALUES (2, N'P01A', CAST(0x0000a19300000000 AS datetime))

GO
INSERT INTO [dbo].[PHIEUTHUEPHONG] ([MaPhieuThue], [MaPhong], [NgayBatDauThue])
	VALUES (3, N'P01A', CAST(0x0000a19300000000 AS datetime))

GO
INSERT INTO [dbo].[PHIEUTHUEPHONG] ([MaPhieuThue], [MaPhong], [NgayBatDauThue])
	VALUES (4, N'P01B', CAST(0x0000a1a801272378 AS datetime))

GO
INSERT INTO [dbo].[PHIEUTHUEPHONG] ([MaPhieuThue], [MaPhong], [NgayBatDauThue])
	VALUES (5, N'P01B', CAST(0x0000a1a801276fa4 AS datetime))

GO
SET IDENTITY_INSERT [dbo].[PHIEUTHUEPHONG] OFF
GO

--Table dbo.PHONG

USE [DoanKhachSan]
GO

--Create table and its columns
CREATE TABLE [dbo].[PHONG] (
	[MaPhong] [varchar](50) NULL,
	[TenPhong] [varchar](50) NULL,
	[TinhTrangPhong] [int] NULL,
	[GhiChu] [text] NULL,
	[MaLoaiPhong] [varchar](50) NULL);
GO

INSERT INTO [dbo].[PHONG] ([MaPhong], [TenPhong], [TinhTrangPhong], [GhiChu], [MaLoaiPhong])
	VALUES (N'P01A', N'Phong 01A', 0, N'', N'PLA')

GO
INSERT INTO [dbo].[PHONG] ([MaPhong], [TenPhong], [TinhTrangPhong], [GhiChu], [MaLoaiPhong])
	VALUES (N'P02A', N'Phong 02A', 0, N'', N'PLA')

GO
INSERT INTO [dbo].[PHONG] ([MaPhong], [TenPhong], [TinhTrangPhong], [GhiChu], [MaLoaiPhong])
	VALUES (N'P03A', N'Phong 03A', 0, N'', N'PLA')

GO
INSERT INTO [dbo].[PHONG] ([MaPhong], [TenPhong], [TinhTrangPhong], [GhiChu], [MaLoaiPhong])
	VALUES (N'P01B', N'Phong 01B', 0, N'', N'PLB')

GO
INSERT INTO [dbo].[PHONG] ([MaPhong], [TenPhong], [TinhTrangPhong], [GhiChu], [MaLoaiPhong])
	VALUES (N'P02B', N'Phong 02B', 0, N'', N'PLB')

GO
INSERT INTO [dbo].[PHONG] ([MaPhong], [TenPhong], [TinhTrangPhong], [GhiChu], [MaLoaiPhong])
	VALUES (N'P03B', N'Phong 03B', 0, N'', N'PLB')

GO

--Executing Entities
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create proc [dbo].[sp_SuaPhong]
@MaPhong varchar(50),
@TenPhong varchar(50),
@MaLoaiPhong varchar(50),
@GhiChu text
AS
BEGIN
/*
	if exists (select MaPhong from Phong where MaPhong=@MaPhong)
		begin
			select ErrMsg=N'Mã Phòng Đã Tồn Tại' 
			return
		end*/
	update  Phong set MaPhong = @MaPhong,TenPhong = @TenPhong,TinhTrangPhong = 0,GhiChu = @GhiChu,MaLoaiPhong = @MaLoaiPhong
	where MaPhong = @MaPhong
		select ErrMsg=N'Sửa Phòng Thành Công'
		return
END





GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create proc [dbo].[sp_ThemKhachHang]
@TenKhachHang varchar(50),
@CMND varchar(50),
@DiaChi varchar(50),
@MaLoaiKhach varchar(50)
AS
BEGIN
	insert into KhachHang values (@TenKhachHang,@CMND,@DiaChi,@MaLoaiKhach)
		select ErrMsg=N'Thêm Khách Hàng Thành Công'
END



GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create proc[dbo].[sp_ThemLoaiKhach]
@MaLoaiKhach varchar(50),
@TenLoaiKhach varchar(50),
@HeSoKhach float
AS
BEGIN
	if exists (select MaLoaiKhach from LoaiKhach where MaLoaiKhach=@MaLoaiKhach)
		begin
			select ErrMsg=N'Mã Loại Khách Đã Tồn Tại' 
			return
		end
	insert into LoaiKhach values (@MaLoaiKhach,@TenLoaiKhach,@HeSoKhach)
		select ErrMsg=N'Thêm Khách Hàng Thành Công'
		return
END

GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create proc [dbo].[sp_ThemLoaiPhong]
@MaLoaiPhong varchar(50),
@TenLoaiPhong varchar(50),
@GiaLoaiPhong int,
@SoKhachToiDa int,
@TyLePhuThu float
AS
BEGIN
	if exists (select MaLoaiPhong from LoaiPhong where MaLoaiPhong=@MaLoaiPhong)
		begin
			select ErrMsg=N'Mã Loại Phòng Đã Tồn Tại' 
			return
		end
	insert into LoaiPhong values (@MaLoaiPhong,@TenLoaiPhong,@GiaLoaiPhong,@SoKhachToiDa,@TyLePhuThu)
		select ErrMsg=N'Thêm Loại Phòng Thành Công'
		return
END




GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create proc [dbo].[sp_ThemPhong]
@MaPhong varchar(50),
@TenPhong varchar(50),
@MaLoaiPhong varchar(50),
@GhiChu text
AS
BEGIN
	if exists (select MaPhong from Phong where MaPhong=@MaPhong)
		begin
			select ErrMsg=N'Mã Phòng Đã Tồn Tại' 
			return
		end
	insert into Phong values (@MaPhong,@TenPhong,0,@GhiChu,@MaLoaiPhong)
		select ErrMsg=N'Thêm Phòng Thành Công'
		return
END




GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create proc [dbo].[sp_XoaLoaiKhach]
	@MaLoaiKhach varchar(50)
AS
	begin
	delete from LoaiKhach where MaLoaiKhach=@MaLoaiKhach
	delete from KhachHang where KhachHang.MaLoaiKhach = @MaLoaiKhach
	select ErrMsg=N'Xóa Loại Khách Thành Công.'
	end


GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create proc [dbo].[sp_XoaPhong]
	@MaPhong varchar(50)
AS
	begin
	delete from Phong where MaPhong=@MaPhong
	select ErrMsg=N'Xóa Phòng Thành Công.'
	end


GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE proc[dbo].[sp_ThemPhieuThuePhong]
@TenKhachHang varchar(50),
@CMND varchar(50),
@DiaChi varchar(50),
@MaLoaiKhach varchar(50),
@MaPhong varchar(50),
@NgayThuePhong datetime
AS
BEGIN
	begin
	Declare @MaKhachHang int,@MaPhieuThuePhong int
	insert into KhachHang values (@TenKhachHang,@CMND,@DiaChi,@MaLoaiKhach)
	 set @MaKhachHang = (SELECT IDENT_CURRENT ('KhachHang'))
	end
	begin
	insert into PHIEUTHUEPHONG values (@MaPhong, @NgayThuePhong)
	set @MaPhieuThuePhong =( SELECT IDENT_CURRENT ('PhieuThuePhong')) 
	end
	begin
	insert into CHITIETPHIEUTHUE values (@MaKhachHang,@MaPhieuThuePhong)
		select ErrMsg=N'Thêm Khách Hàng Thành Công'
	end
		return

END



GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE proc [dbo].[test]
AS
	begin
SELECT IDENT_CURRENT('KhachHang') as makhachhang
	end
GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE proc [dbo].[sp_LoadDSPhongTheoLoaiPhong]
@MaLoaiPhong varchar(50)
AS
BEGIN
		select * from Phong
		where MaLoaiPhong = @MaLoaiPhong
		return
END





GO

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE proc [dbo].[sp_LoadGiaTheoLoaiPhong]
@MaLoaiPhong varchar(50)
AS
BEGIN
		select * from LoaiPhong
		where MaLoaiPhong = @MaLoaiPhong
		return
END





GO

GO

--Indexes of table dbo.CHITIETHOADON
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TABLE [dbo].[CHITIETHOADON] ADD CONSTRAINT [PK_CHITIETHOADON] PRIMARY KEY CLUSTERED ([MaChiTietHoaDon]) 
GO

--Indexes of table dbo.CHITIETPHIEUTHUE
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TABLE [dbo].[CHITIETPHIEUTHUE] ADD CONSTRAINT [PK_CHITIETPHIEUTHUE] PRIMARY KEY CLUSTERED ([MaChiTietPhieuThue]) 
GO

--Indexes of table dbo.KHACHHANG
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TABLE [dbo].[KHACHHANG] ADD CONSTRAINT [PK_KHACHHANG] PRIMARY KEY CLUSTERED ([MaKhachHang]) 
GO

--Indexes of table dbo.LOAIPHONG
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TABLE [dbo].[LOAIPHONG] ADD CONSTRAINT [PK_LOAIPHONG] PRIMARY KEY CLUSTERED ([MaLoaiPhong]) 
GO

--Indexes of table dbo.PHIEUTHUEPHONG
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TABLE [dbo].[PHIEUTHUEPHONG] ADD CONSTRAINT [PK_PHIEUTHUEPHONG] PRIMARY KEY CLUSTERED ([MaPhieuThue]) 
GO
